﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace _058.Excel操作
{
    public class Gelly
    {

        public static void Removed(string filePath)
        {
            //string filePath = @"C:\example.xlsx"; // Excel 文件路径
            string sheetName = "Sheet1"; // Excel 表格名称

            Application application = new Application();
            Workbook workbook = application.Workbooks.Open(filePath);
            Worksheet worksheet = (Worksheet)workbook.Sheets[sheetName];

            // 获取当前表格中的数据范围
            Range range = worksheet.UsedRange;

            object[,] values = null;
            int rowCount;
            int columnCount;
            try
            {
                // 将当前表格中的所有数据存储到维数组中
                values = range.Value as object[,];
            }
            catch (System.Runtime.InteropServices.SafeArrayTypeMismatchException ex)
            {
                rowCount = range.Rows.Count;
                columnCount = range.Columns.Count;

                List<object[]> tempValues = new List<object[]>();
                for (int i = 1; i <= rowCount; i++)
                {
                    object[] rowValues = new object[columnCount];
                    for (int j = 1; j <= columnCount; j++)
                    {
                        if (range.Cells[i, j].Value != null)
                        {
                            rowValues[j - 1] = Convert.ToString(range.Cells[i, j].Value);
                        }
                        else
                        {
                            rowValues[j - 1] = string.Empty;
                        }
                    }
                    tempValues.Add(rowValues);
                }

                values = ConvertTo2DArray(tempValues);
            }

            rowCount = values.GetLength(0);
            columnCount = values.GetLength(1);

            // 根据特定列去除重复行，可自定义列索引
            List<object[]> resultValues = RemoveDuplicateRows(values, 0);

            // 将处理后的结果重新写入表格中
            Range resultRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowCount, columnCount]];
            resultRange.ClearContents();
            resultRange.Value2 = ConvertTo2DArray(resultValues);

            workbook.Save();
            workbook.Close();
            application.Quit();
            Marshal.ReleaseComObject(application);
        }
        static List<object[]> RemoveDuplicateRows(object[,] values, int columnIndex)
        {
            List<object[]> resultValues = new List<object[]>();

            // 使用 HashSet 存储已经出现过的值
            HashSet<object> hashSet = new HashSet<object>();
            for (int i = 1; i <= values.GetLength(0); i++)
            {
                object[] rowValues = new object[values.GetLength(1)];
                bool isDuplicate = false;
                for (int j = 1; j <= values.GetLength(1); j++)
                {
                    rowValues[j - 1] = values[i, j];

                    if (j == columnIndex + 1)
                    {
                        if (!hashSet.Contains(values[i, j]))
                        {
                            hashSet.Add(values[i, j]);
                        }
                        else
                        {
                            isDuplicate = true;
                            break;
                        }
                    }
                }
                if (!isDuplicate)
                {
                    resultValues.Add(rowValues);
                }
            }

            return resultValues;
        }

        static object[,] ConvertTo2DArray(List<object[]> values)
        {
            int rowCount = values.Count;
            int columnCount = values[0].Length;
            object[,] result = new object[rowCount, columnCount];
            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < columnCount; j++)
                {
                    result[i, j] = values[i][j];
                }
            }
            return result;
        }
        /**
         * 合并
         */
        public static void Merge(string file)
        {
            // 文件路径和名称
            string filePath = @"D:\ExcelData\"+ file;
            string newFilePath = @"D:\ExcelData\DTCSUM\"+ file;

            // 创建Excel对象
            Application excel = new Application();
            Workbook workbook = excel.Workbooks.Open(filePath);

            // 新建一个工作表，用于汇总数据
            Worksheet newWorksheet = workbook.Sheets.Add(Type.Missing, workbook.Sheets[1], Type.Missing, Type.Missing);
            newWorksheet.Name = "Merged Data";

            // 循环遍历所有的工作表
            foreach (Worksheet worksheet in workbook.Sheets)
            {
                if (worksheet.Name != newWorksheet.Name) // 排除新建的汇总工作表
                {
                    Console.WriteLine(worksheet.Name);
                    Range range = worksheet.UsedRange;

                    // 将当前工作表的数据复制到新的工作表中
                    int lastRowIndex = newWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row + 1;
                    range.Copy(Type.Missing);
                    newWorksheet.Range["A" + lastRowIndex.ToString()].PasteSpecial(XlPasteType.xlPasteAll);

                }
            }

            // 保存新的Excel文件
            workbook.SaveAs(newFilePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workbook.Close(false, Type.Missing, Type.Missing);
            excel.Quit();

            // 释放资源
            releaseObject(newWorksheet);
            releaseObject(workbook);
            releaseObject(excel);
        }
        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}
